﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace Carbon.Web.Forms
{
    public class PersistanceService : IPersistanceService
    {
        private SqlConnection m_SqlConnection = default(SqlConnection);
        private bool m_SqlConnection_calculated = false;
        public SqlConnection SqlConnection
        {
            get
            {
                if (!m_SqlConnection_calculated)
                {

                    String connectionString = Configuration.Instance.ConnectionStringValue;
                    if (string.IsNullOrEmpty(connectionString)) throw new ArgumentOutOfRangeException("connectionString");
                    m_SqlConnection = new SqlConnection(connectionString);
                    m_SqlConnection.Open();
                    m_SqlConnection_calculated = true;
                }

                return m_SqlConnection;
            }
        }

        public byte[] LoadBinaryData(Object id, Guid key)
        {
            return LoadFile(new FileReference(id, key, string.Empty));
        }

        public Object StoreBinaryData(Guid key, byte[] data)
        {
            return StoreFile(key, string.Empty, data).Id;
        }

        public void FreeIdentifier(Object id, Guid key)
        {
            FreeFile(new FileReference(id, key, string.Empty));
        }

        public void UpdateBinaryData(Object id, Guid key, byte[] data)
        {
            using (SqlCommand command = SqlConnection.CreateCommand())
            {
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "[dbo].[Blobs.Update]";
                command.Parameters.Add(new SqlParameter("id", SqlDbType.BigInt) { Value = id });
                command.Parameters.Add(new SqlParameter("key", SqlDbType.UniqueIdentifier) { Value = key });
                command.Parameters.Add(new SqlParameter("name", SqlDbType.NVarChar) { Value = string.Empty });
                command.Parameters.Add(new SqlParameter("data", SqlDbType.Binary) { Value = data });

                command.ExecuteScalar();
            }
        }

        public FileReference StoreFile(Guid key, string name, byte[] content)
        {
            if ((name != null && name.Length <= 256) == false) throw new InvalidOperationException("(name != null && name.Length <= 256) == false");

            using (SqlCommand command = SqlConnection.CreateCommand())
            {
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "[dbo].[Blobs.Insert]";
                command.Parameters.Add(new SqlParameter("key", System.Data.SqlDbType.UniqueIdentifier) { Value = key });
                command.Parameters.Add(new SqlParameter("name", System.Data.SqlDbType.NVarChar) { Value = name });
                command.Parameters.Add(new SqlParameter("data", System.Data.SqlDbType.VarBinary) { Value = content });

                object result = command.ExecuteScalar();

                return new FileReference(result, key, name);
            }
        }

        public byte[] LoadFile(FileReference reference)
        {
            using (SqlCommand command = SqlConnection.CreateCommand())
            {
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "[dbo].[Blobs.Get]";
                command.Parameters.Add(new SqlParameter("id", SqlDbType.BigInt) { Value = reference.Id });
                command.Parameters.Add(new SqlParameter("key", SqlDbType.UniqueIdentifier) { Value = reference.Key });

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        byte[] data = (byte[])reader["Data"];
                        String fileName = (String)reader["Name"];
                        if (data == null) throw new NullReferenceException("data");

                        reference.Name = fileName;

                        return data;
                    }
                }

                throw new ArgumentOutOfRangeException("Record not found");
            }
        }

        public void FreeFile(FileReference reference)
        {
            using (SqlCommand command = SqlConnection.CreateCommand())
            {
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "[dbo].[Blobs.Delete]";
                command.Parameters.Add(new SqlParameter("id", SqlDbType.BigInt) { Value = reference.Id });
                command.Parameters.Add(new SqlParameter("key", SqlDbType.UniqueIdentifier) { Value = reference.Key });

                command.ExecuteScalar();
            }
        }

        public void Dispose()
        {
            if (m_SqlConnection_calculated)
                m_SqlConnection.Dispose();
        }
    }
}
